﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SQLite;
namespace mysix
{
    internal class SchoolDB
    {

        private SQLiteConnection connection;
        string connectionString = "Data Source=mydatabase.db;Version=3;";

        public SchoolDB()
        {
            // 在构造函数中初始化数据库
            connection = new SQLiteConnection(connectionString);
        }
        public DataTable GetSchoolData()
        {
            // 创建一个 DataTable 以获取 School 表的数据
            DataTable schoolDataTable = new DataTable();

            try
            {
                connection.Open();

                string selectSchoolSQL = "SELECT SchoolId, SchoolName FROM School";
                SQLiteCommand selectSchoolCmd = new SQLiteCommand(selectSchoolSQL, connection);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectSchoolCmd))
                {
                    adapter.Fill(schoolDataTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return schoolDataTable;
        }
        public void AddSchool(int schoolId, string schoolName)
        {
            try
            {
                connection.Open();

                string insertSchoolSQL = "INSERT INTO School (SchoolId, SchoolName) VALUES (@SchoolId, @SchoolName)";
                SQLiteCommand insertSchoolCmd = new SQLiteCommand(insertSchoolSQL, connection);
                insertSchoolCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                insertSchoolCmd.Parameters.AddWithValue("@SchoolName", schoolName);
                insertSchoolCmd.ExecuteNonQuery();

                Console.WriteLine("School added successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void DeleteSchool(int schoolId)
        {
            try
            {
                connection.Open();

                string deleteSchoolSQL = "DELETE FROM School WHERE SchoolId = @SchoolId";
                SQLiteCommand deleteSchoolCmd = new SQLiteCommand(deleteSchoolSQL, connection);
                deleteSchoolCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                deleteSchoolCmd.ExecuteNonQuery();

                Console.WriteLine("School deleted successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateSchool(int schoolId, string schoolName)
        {
            try
            {
                connection.Open();

                string updateSchoolSQL = "UPDATE School SET SchoolName = @SchoolName WHERE SchoolId = @SchoolId";
                SQLiteCommand updateSchoolCmd = new SQLiteCommand(updateSchoolSQL, connection);
                updateSchoolCmd.Parameters.AddWithValue("@SchoolName", schoolName);
                updateSchoolCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                updateSchoolCmd.ExecuteNonQuery();

                Console.WriteLine("School updated successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void SearchSchool(int schoolId)
        {
            try
            {
                connection.Open();

                string selectSchoolSQL = "SELECT SchoolId, SchoolName FROM School WHERE SchoolId = @SchoolId";
                SQLiteCommand selectSchoolCmd = new SQLiteCommand(selectSchoolSQL, connection);
                selectSchoolCmd.Parameters.AddWithValue("@SchoolId", schoolId);

                using (SQLiteDataReader reader = selectSchoolCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        MessageBox.Show("学校名称为："+name);
                        Console.WriteLine($"SchoolId: {id}, SchoolName: {name}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        public DataTable Search1School(int schoolId)
        {
            DataTable resultTable = new DataTable();

            try
            {
                connection.Open();

                string selectSchoolSQL = "SELECT SchoolId, SchoolName FROM School WHERE SchoolId = @SchoolId";
                SQLiteCommand selectSchoolCmd = new SQLiteCommand(selectSchoolSQL, connection);
                selectSchoolCmd.Parameters.AddWithValue("@SchoolId", schoolId);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectSchoolCmd))
                {
                    adapter.Fill(resultTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return resultTable;
        }



    }
}
